package com.project;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Set;
import java.util.regex.*;

public class DB_Airline extends DB_Operator {

    /**
     * Default constructor
     */
    public DB_Airline() {
    }

    /**
     * @param input 输入
     * @return 返回
     */
    public boolean add(List<String> input) {
        if (input.size() != 4) {
            return false;
        }
        connect();
        String sql = "INSERT INTO `Airline` (`fid`, `date`, `position`, `remain`) VALUES (?,?,?,?)";
        try (PreparedStatement PStmt = con.prepareStatement(sql)) {
            PStmt.setInt(1, Integer.parseInt(input.get(0)));
            PStmt.setString(2, input.get(1));
            PStmt.setString(3, input.get(2));
            PStmt.setInt(4, Integer.parseInt(input.get(3)));
            int rowsAffected = PStmt.executeUpdate();
            close();
            return rowsAffected > 0;
        } catch (SQLException e) {
            System.err.println("出现异常：" + e.getMessage());
            close();
            return false;
        }
    }

    /**
     * @param input 输入
     * @return 返回
     */
    public boolean delete(List<String> input) {
        if (input.isEmpty()) {
            return false;
        }
        connect();
        String deleteSql = "DELETE FROM `Airline` WHERE `aid` =?";
        String maxIdSql = "SELECT MAX(aid) FROM `Airline`";
        String updateAutoIncrementSql = "ALTER TABLE `Airline` AUTO_INCREMENT =?";
        try (PreparedStatement deletePStmt = con.prepareStatement(deleteSql);
             PreparedStatement maxIdPStmt = con.prepareStatement(maxIdSql);
             PreparedStatement updatePStmt = con.prepareStatement(updateAutoIncrementSql)) {
            deletePStmt.setString(1, input.getFirst());
            int rowsAffected = deletePStmt.executeUpdate();
    
            ResultSet rs = maxIdPStmt.executeQuery();
            int maxId = 1;
            if (rs.next()) {
                maxId = rs.getInt(1) + 1;
            }
            updatePStmt.setInt(1, maxId);
            updatePStmt.executeUpdate();
    
            close();
            return rowsAffected > 0;
        } catch (SQLException e) {
            System.err.println("出现异常：" + e.getMessage());
            close();
            return false;
        }
    }


    /**
     * @param input 输入
     * @return 返回
     */
    public List<String> find(List<String> input) {
        List<String> result = new ArrayList<>();
        if (input == null || input.isEmpty()) {
            return result;
        }

        connect();
        StringBuilder sql = new StringBuilder("SELECT * FROM Airline WHERE ");
        List<Object> params = new ArrayList<>();

        try {
            for (int i = 0; i < input.size(); i++) {
                Pattern pattern = Pattern.compile("(<=|>=|=|<|>)");
                Matcher matcher = pattern.matcher(input.get(i));
                if (matcher.find()) {
                    String operator = matcher.group();
                    String[] parts = input.get(i).split(Pattern.quote(operator), 2);
                    String field = parts[0].trim();
                    String value = parts[1].trim();

                    if (!Set.of("aid", "fid", "date", "position", "remain", "1").contains(field.toLowerCase())) {
                        throw new IllegalArgumentException("Invalid field name: " + field);
                    }

                    sql.append(field).append(operator).append("?");
                    // 处理值类型转换
                    Object param;
                    if (Set.of("aid", "fid", "remain", "1").contains(field)) {  // 整数字段
                        try {
                            param = Integer.parseInt(value);
                        } catch (NumberFormatException e) {
                            System.err.println("字段[" + field + "]需要整数值: " + value);
                            continue;
                        }
                    } else {  // 字符串字段（包括date）
                        param = value.trim();
                    }

                    // 构建SQL和参数
                    params.add(param);

                    if (i < input.size() - 1) {
                        sql.append(" AND ");
                    }
                } else {
                    throw new IllegalArgumentException("Invalid condition format: " + input.get(i));
                }
            }

            sql.append(" ORDER BY `aid` ASC");

            try (PreparedStatement PStmt = con.prepareStatement(sql.toString())) {
                for (int i = 0; i < params.size(); i++) {
                    Object param = params.get(i);
                    if (param instanceof Integer) {
                        PStmt.setInt(i + 1, (Integer) param);
                    } else {
                        PStmt.setString(i + 1, (String) param);
                    }
                }
                ResultSet rs = PStmt.executeQuery();

                while (rs.next()) {
                    String row = String.join(",",
                            rs.getString("aid"),
                            rs.getString("fid"),
                            rs.getString("date"),
                            rs.getString("position"),
                            String.valueOf(rs.getInt("remain"))
                    );
                    result.add(row);
                }
            }
        } catch (SQLException | IllegalArgumentException e) {
            System.err.println("出现异常：" + e.getMessage());
        } finally {
            close();
        }
        return result;
    }

    /**
     * @param conditionColumn 条件
     * @param conditionValue 值
     * @param returnList 返回
     */
    public void find(String conditionColumn, String conditionValue, List<String> returnList) {
        connect();
        String sql = "SELECT * FROM `Airline` WHERE true";

        if (!conditionColumn.isEmpty() &&!conditionValue.isEmpty()) {
            sql += " AND `" + conditionColumn + "` =?";
        }
        try (PreparedStatement PStmt = con.prepareStatement(sql)) {
            if (!conditionColumn.isEmpty() &&!conditionValue.isEmpty()) {
                PStmt.setString(1, conditionValue);
            }
            ResultSet rs = PStmt.executeQuery();
            while (rs.next()) {
                returnList.add(rs.getString("aid"));
                returnList.add(rs.getString("fid"));
                returnList.add(rs.getString("date"));
                returnList.add(rs.getString("position"));
                returnList.add(String.valueOf(rs.getInt("remain")));
            }
            close();
        } catch (SQLException e) {
            System.err.println("出现异常：" + e.getMessage());
            close();
        }
    }

    /**
     * @param input 输入
     * @return 返回
     */
    public boolean update(List<String> input) {
        if (input.size() != 7) {
            return false;
        }
        connect();
        String sql = "UPDATE `Airline` SET `fid` =?, `date` =?, `position` =?, `remain` =? WHERE `aid` =?";
        try (PreparedStatement PStmt = con.prepareStatement(sql)) {
            PStmt.setString(1, input.get(1));
            // 假设输入的年、月、日分别为input.get(2)、input.get(3)、input.get(4)，组合成日期格式
            PStmt.setString(2, input.get(2) + "-" + input.get(3) + "-" + input.get(4));
            PStmt.setString(3, input.get(5));
            PStmt.setInt(4, Integer.parseInt(input.get(6)));
            PStmt.setString(5, input.get(0));
            int rowsAffected = PStmt.executeUpdate();
            close();
            return rowsAffected > 0;
        } catch (SQLException e) {
            System.err.println("出现异常：" + e.getMessage());
            close();
            return false;
        }
    }

    // 新增方法：查询Flight表中的fid
    public List<String> getFlightIds() {
        connect();
        String sql = "SELECT fid FROM Flight";
        List<String> fidList = new ArrayList<>();
        try (PreparedStatement PStmt = con.prepareStatement(sql)) {
            ResultSet rs = PStmt.executeQuery();
            while (rs.next()) {
                fidList.add(rs.getString("fid"));
            }
            close();
        } catch (SQLException e) {
            System.err.println("出现异常：" + e.getMessage());
        }
        return fidList;
    }
}